#!/opt/local/bin/perl -w
use strict;

package ChadoCV;

our (@ISA, @EXPORT, $VERSION);

use Exporter;
$VERSION= 1.00;
@ISA = qw(Exporter);
@EXPORT= qw (get_cvterm_lookup);
1;

require ChadoConnect;
my $dbh = ChadoConnect::make_con($ARGV[0]);

#### Defining cvterm object
{ 
  package cvterm;
  use Moose;
#  use MooseX::StrictConstructor; # Prompts error if try to access undeclared Attribute / commented because of install problems. Uncomment once fixed.
  has 'name', is => 'ro', isa => 'Str';
  has 'chado_id',  is => 'ro', isa => 'Str';
  has 'obo_id', is => 'ro', isa => 'Str';
  has 'chado_ontology', is => 'ro', isa => 'Str';
  has 'obo_namespace', is => 'ro', isa => 'Str';
  has 'webcv', is => 'ro', isa => 'Str'
}

{ # An object too far?
  package cvterm_lookup;
  use Moose;
#  use MooseX::StrictConstructor; # Prompts error if try to access undeclared Attribute / commented because of install problems. Uncomment once fixed.
  has 'cvterm_cid', is => 'ro', isa => 'HashRef';
  has 'cvterm_oboid', is => 'ro', isa => 'HashRef';
  has 'cvterm_idp_name', is => 'ro', isa => 'HashRef'
}

# roll hash of cvterm objects keyed on cid.  
# Future modifications - this should take arguments to direct what is grabbed: db.name, cv.name, cvtemprop.value where type.name=webcv.
## May also be worth returning an object that bundles in arguments as attributes.  That way it is always possible to inspect the has 
# Better to return an object with multiple hashes with alternative keys?
### cid, OBO_id, name + idp;

sub get_cvterm_lookup {
  
  # Populate cvterm model - GO CC development and anatomy

my  $sth = $dbh->prepare('SELECT cv.name as chado_cv, c.name, db.name as idp, dbx.accession as acc, c.cvterm_id as chado_id 
FROM cvterm c
JOIN dbxref dbx ON (c.dbxref_id = dbx.dbxref_id)
JOIN db ON (dbx.db_id=db.db_id)
JOIN cv ON (c.cv_id=cv.cv_id)
-- WHERE db.name in (\'GO\', \'FBbt\', \'FBdv\') -- Leaving this out to get the bigger picture. For generic - this should be configurable.
AND c.is_obsolete = \'0\'  -- note - this is an integer, NOT a boolean FB CHADO cvterm table !!!
AND is_relationshiptype = \'0\' -- note - this is an integer, NOT a boolean 
AND cv.name IN (\'FlyBase anatomy CV\', \'FlyBase development CV\', \'cellular_component\')'); # add outer join to bring in FBcv fake namespaces ?

  $sth->execute or die "WARNING: ERR: Unable to execute query\n";

  my %cvterm_cid; # a hash of cvterm objects keyed on chado_id.  This is safe and useful internally - but pretty useless for querying.
  my %cvterm_oboid;	# a hash of cvterm objects keyed on OBO_id. Useful for querying.
  my %cvterm_idp_name; # a hash of hashes of cvterm objects keyed on {idp}{name}
  while (my $hash_ref = $sth->fetchrow_hashref) {
    my $cvterm = cvterm->new(
      name => $hash_ref->{'name'},
      obo_id => $hash_ref->{'idp'}.':'.$hash_ref->{'acc'},
      obo_ontology => $hash_ref->{'idp'},
      chado_ontology => $hash_ref->{'chado_cv'}, # same as obo namespace
     );
    $cvterm_cid{$hash_ref->{'chado_id'}} = $cvterm;
    $cvterm_oboid{$hash_ref->{'idp'}.':'.$hash_ref->{'acc'}}=$cvterm;
    $cvterm_idp_name{$hash_ref->{'idp'}}{$hash_ref->{'name'}}=$cvterm
  }

  $sth = $dbh->prepare('SELECT cv.name as chado_cv, c.name, db.name as idp, dbx.accession as acc, c.cvterm_id as chado_id, cvtp.value as webcv 
FROM cvterm c
JOIN dbxref dbx ON (c.dbxref_id = dbx.dbxref_id)
JOIN db ON (dbx.db_id=db.db_id)
JOIN cv ON (c.cv_id=cv.cv_id)
JOIN cvtermprop cvtp ON (c.cvterm_id=cvtp.cvterm_id)
JOIN cvterm cvtptype ON (cvtptype.cvterm_id=cvtp.type_id)
WHERE db.name =\'FBcv\' -- Clause not actually needed, at least not currenty,  But keeping in for safety
AND c.is_obsolete = \'0\'  -- note - this is an integer, NOT a boolean FB CHADO cvterm table !!!
AND c.is_relationshiptype = \'0\' -- note - this is an integer, NOT a boolean 
AND cv.name = \'FlyBase miscellaneous CV\'
AND cvtptype.name=\'webcv\';');

  $sth->execute or die "WARNING: ERR: Unable to execute query\n";
  
  while (my $hash_ref = $sth->fetchrow_hashref) {
    my $cvterm = cvterm->new(
      name => $hash_ref->{'name'},
      obo_id => $hash_ref->{'idp'}.':'.$hash_ref->{'acc'},
      obo_ontology => $hash_ref->{'idp'},
      chado_ontology => $hash_ref->{'chado_cv'}, # FBcv obo default namespace
      chado_webcv => $hash_ref->{'webcv'}
     );
    $cvterm_cid{$hash_ref->{'chado_id'}} = $cvterm;
    $cvterm_oboid{$hash_ref->{'idp'}.':'.$hash_ref->{'acc'}}=$cvterm;
    $cvterm_idp_name{$hash_ref->{'idp'}}{$hash_ref->{'name'}}=$cvterm;
  }
  my $cvterm_lookup = cvterm_lookup->new(cvterm_cid => \%cvterm_cid, cvterm_oboid => \%cvterm_oboid, cvterm_idp_name =>\%cvterm_idp_name);
  return $cvterm_lookup;
}

1; # module must return true;
